package com.okq.dao;

import com.okq.dao.base.Impl.BaseDao;
import com.okq.entity.Product;
import com.okq.entity.ProductBatchNumber;
import com.okq.entity.ProductChart;
import com.okq.entity.ProductTypeNumber;
import com.okq.util.page.Page;
import org.apache.commons.lang.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 描述 : 产品Dao
 * 作者 : zdl
 * 日期 : 2017/6/29 11:46
 */
@Repository
public class ProductDao extends BaseDao {

    public void saveProduct(Product product) {
        super.save(product);
    }

    public void deleteProduct(Product product) {
        super.delete(product);
    }

    public void updateProduct(Product product) {
        super.update(product);
    }

    public void saveOrUpdateProduct(Product product) {
        super.saveOrUpdate(product);
    }

    public Product getProductById(String id) {
        return (Product) super.get(Product.class, id);
    }

    public List<Product> listAllProduct() {
        String hql = "FROM Product WHERE isDelete=0";
        return super.find(hql);
    }

    /**
     * 分页查询
     *
     * @param page    分页对象
     * @param product 产品查询条件
     * @return page
     */
    public Page<Product> pageByProduct(Page<Product> page, Product product) {
        StringBuilder hqlSb = new StringBuilder();
        hqlSb.append("FROM Product p WHERE p.isDelete = 0 ");
        if (product != null) {
            Map<String, Object> param = new HashMap<>();
            String productionId = product.getProductionId();
            if (StringUtils.isNotBlank(productionId)) {
                hqlSb.append(" AND p.productionId = ?1 ");
                param.put("1", productionId);
            }
            String code = product.getCode();
            if (StringUtils.isNotBlank(code)) {
                hqlSb.append(" AND p.code LIKE ?3 ");
                param.put("3", "%" + code + "%");
            }
            hqlSb.append("ORDER BY p.code ");
            return super.findByPage(page, hqlSb.toString(), param);
        }
        return page;
    }

    public Product getProductByCode(String code) {
        if (StringUtils.isNotBlank(code)) {
            String hql = "FROM Product p WHERE p.isDelete = 0 AND p.code = '" + code + "'";
            return (Product) super.get(hql);
        }
        return null;
    }

    /**
     * 根据生产Id查询产品list
     *
     * @param pId 生产Id
     * @return list
     */
    public List<Product> listProductByPid(String pId) {
        if (StringUtils.isNotBlank(pId)) {
            String hql = "FROM Product p WHERE p.isDelete = 0 AND p.productionId = '" + pId + "'";
            return super.find(hql);
        }
        return null;
    }

    /**
     * 按天分组查询每天生产数量以及合格数和不合格数
     *
     * @param product 产品
     * @return list
     */
    public List<ProductChart> listProductByCondition(Product product) {
        StringBuilder sqlSb = new StringBuilder();
        sqlSb.append("SELECT t2.days AS days,t3.checkResult AS checkResult,IFNULL(t3.num,0) AS number FROM ");
        sqlSb.append("(SELECT @cdate \\:= date_add(@cdate,INTERVAL -1 DAY) days FROM (SELECT @cdate \\:= CURDATE() FROM tb_product ) t1 ) t2 ");
        sqlSb.append(" LEFT JOIN ");
        sqlSb.append("(SELECT DATE_FORMAT(checkTime,'%Y-%m-%d') AS checkDay,p.checkResult,COUNT(1) AS num FROM tb_product p ");
        sqlSb.append("WHERE p.isDelete = 0 AND p.checkResult <>0 ");
        String startTime = "";
        if (product != null) {
            String productionIds = product.getProductionIds();
            if (StringUtils.isNotBlank(productionIds)) {
                sqlSb.append(" AND p.productionId IN (").append(productionIds).append(") ");
            }
            Integer productType = product.getProductType();
            if (productType != null && productType != -1) {
                sqlSb.append(" AND p.productType = ").append(productType);
            }
            startTime = product.getStartTime();
            if (StringUtils.isNotBlank(startTime)) {
                sqlSb.append(" AND p.checkTime >= '").append(startTime).append("' ");
            }
            String endTime = product.getEndTime();
            if (StringUtils.isNotBlank(endTime)) {
                sqlSb.append(" AND p.checkTime <= '").append(endTime).append("' ");
            }
            //操作员
            String operator = product.getOperator();
            if (StringUtils.isNotBlank(operator)) {
                sqlSb.append(" AND ( p.weldId= '").append(operator).append("' ");
                sqlSb.append(" OR p.assembleId= '").append(operator).append("' ");
                sqlSb.append(" OR p.debugId= '").append(operator).append("' ");
                sqlSb.append(" OR p.checkId= '").append(operator).append("' )");
            }
            String comCode = product.getComCode();
            if (StringUtils.isNotBlank(comCode)) {
                sqlSb.append(" AND p.comCode = '").append(comCode).append("' ");
            }
        }
        sqlSb.append("GROUP BY checkDay,checkResult ORDER BY checkDay ASC) t3 ");
        sqlSb.append("ON t2.days = t3.checkDay WHERE ");
        if (StringUtils.isNotBlank(startTime)) {
            sqlSb.append("t2.days >= DATE_FORMAT('").append(startTime).append("','%Y-%m-%d')");
        } else {
            sqlSb.append("t2.days >= (SELECT DATE_FORMAT(MIN(checkTime),'%Y-%m-%d') FROM tb_product where isDelete=0)");
        }
        SQLQuery sqlQuery = super.getSession().createSQLQuery(sqlSb.toString());
        return sqlQuery.addScalar("days", StandardBasicTypes.STRING).addScalar("checkResult", StandardBasicTypes.INTEGER)
                .addScalar("number", StandardBasicTypes.INTEGER).setResultTransformer(Transformers.aliasToBean(ProductChart.class)).list();
    }

    /**
     * 根据产品类型查询库存数量
     *
     * @param page        分页
     * @param productType 产品类型
     * @return page
     */
    @SuppressWarnings("all")
    public Page<ProductTypeNumber> pageByProductType(Page<ProductTypeNumber> page, Integer productType) {
        StringBuilder hqlSb = new StringBuilder();
        hqlSb.append("FROM ProductTypeNumber pt WHERE 1=1 ");
        if (productType != null && productType != -1) {
            Map<String, Object> param = new HashMap<>();
            hqlSb.append(" AND productType = ?1 ");
            param.put("1", productType);
            return super.findByPage(page, hqlSb.toString(), param);
        }
        return page;
    }

    /**
     * 根据产品类型，查询每个生产批次的库存数量
     *
     * @param page        分页
     * @param productType 产品类型
     * @return page
     */
    @SuppressWarnings("all")
    public Page<ProductBatchNumber> pageByProductBatch(Page<ProductBatchNumber> page, Integer productType) {
        StringBuilder hqlSb = new StringBuilder();
        hqlSb.append("FROM ProductBatchNumber pt WHERE 1=1 ");
        if (productType != null && productType != -1) {
            Map<String, Object> param = new HashMap<>();
            hqlSb.append(" AND productType = ?1 ");
            param.put("1", productType);
            return super.findByPage(page, hqlSb.toString(), param);
        }
        return page;
    }

}
